use calamine::{Data, Range};
use clickhouse_rs::Block;
use shq_common::prelude::{alpha_to_index, get_ch_pool, index_to_alpha};

use crate::definition::Category;

async fn load_category_from_db<'a>(
    category: &'a Category,
) -> anyhow::Result<Vec<(Vec<String>, u32)>> {
    let sql = format!(
        "select {},{} from {}",
        category.level_col_names.join(","),
        category.id_col_name,
        category.table_name,
    );
    let mut result: Vec<(Vec<String>, u32)> = Vec::new();
    let mut client = get_ch_pool().get_handle().await?;
    let block = client.query(&sql).fetch_all().await?;
    for row in block.rows() {
        let mut level_names: Vec<String> = Vec::new();
        for i in 0..category.level_col_names.len() {
            let cval: String = row.get(i)?;
            level_names.push(cval);
        }
        let id: u32 = row.get(category.id_col_name.as_str())?;
        result.push((level_names, id));
    }
    Ok(result)
}
async fn save_category_to_db<'a>(
    category: &'a Category,
    datas: &'a [(Vec<String>, u32)],
) -> anyhow::Result<()> {
    let mut client = get_ch_pool().get_handle().await?;
    let ids = datas.iter().map(|d| d.1).collect::<Vec<u32>>();
    let mut block = Block::new();
    block = block.add_column(&category.id_col_name, ids);
    for i in 0..category.level_col_names.len() {
        let vals: Vec<&str> = datas.iter().map(|d| d.0[i].as_str()).collect();
        block = block.add_column(category.level_col_names[i].as_str(), vals);
    }
    client.insert(category.table_name.as_str(), block).await?;
    Ok(())
}
pub(super) async fn change_category_to_db<'a>(
    datas: &'a Range<Data>,
    category: &'a Category,
) -> anyhow::Result<Vec<(u32, u32)>> {
    let old_datas = load_category_from_db(category)
        .await
        .or_else(|err| Err(anyhow::anyhow!("从数据库读取分类数据失败:{err}")))?;
    let new_datas = read_category_from_range(datas, category)
        .or_else(|err| Err(anyhow::anyhow!("从excel读取分类数据失败:{err}")))?;
    let mut insert_datas: Vec<(Vec<String>, u32)> = Vec::new();
    let mut max_id = old_datas.iter().map(|d| d.1).max().unwrap_or(0);
    let mut is_change = false;
    let mut result: Vec<(u32, u32)> = Vec::new();
    for (d, cid) in new_datas.into_iter() {
        let old_idx = old_datas.iter().enumerate().find_map(|(i, (old_d, _))| {
            if old_d.len() != d.len() {
                return None;
            }
            if old_d
                .iter()
                .zip(d.iter())
                .all(|(old_val, new_val)| old_val == new_val)
            {
                Some(i)
            } else {
                None
            }
        });
        if let Some(i) = old_idx {
            let old_id = old_datas[i].1;
            result.push((cid, old_id));
        } else {
            max_id += 1;
            let vals: Vec<String> = d.iter().map(|s| s.to_string()).collect::<Vec<String>>();
            insert_datas.push((vals, max_id));
            result.push((cid, max_id));
            is_change = true;
        }
    }
    if is_change {
        save_category_to_db(category, &insert_datas)
            .await
            .or_else(|err| Err(anyhow::anyhow!("保存分类数据失败:{err}")))?;
    }
    Ok(result)
}
fn read_category_from_range<'a>(
    datas: &'a Range<Data>,
    category: &'a Category,
) -> anyhow::Result<Vec<(Vec<String>, u32)>> {
    let start_col = alpha_to_index(&category.start_col) - 1;
    let start_row = category.start_row - 1;
    let col_count = datas.get_size().1 as u32;
    let clen = category.level_col_names.len();
    let mut rows: Vec<Vec<String>> = Vec::new();
    for j in 0..clen {
        let mut level_names: Vec<String> = Vec::new();
        let row = start_row + j as u32;
        let mut prev_name: Option<String> = None;
        for col in start_col..col_count {
            let cval = datas.get_value((row, col)).unwrap_or(&Data::Empty);
            match cval {
                Data::Empty => {
                    if prev_name.is_none() {
                        if j == 0 {
                            anyhow::bail!(
                                "单元格{}{}没有值,请检查导入文件格式",
                                index_to_alpha(col + 1),
                                row + 1
                            );
                        } else {
                            level_names.push("".to_string());
                        }
                    } else {
                        if j == 0 {
                            level_names.push(prev_name.as_deref().unwrap().to_string());
                        } else {
                            let idx1 = col as usize - start_col as usize;
                            let idx2 = idx1 - 1;
                            if rows[j - 1][idx1] == rows[j - 1][idx2] {
                                level_names.push(prev_name.as_deref().unwrap().to_string());
                            } else {
                                level_names.push("".to_string());
                            }
                        }
                    }
                }
                Data::String(s) => {
                    let title = s.trim().to_string();
                    level_names.push(title.clone());
                    prev_name = Some(title);
                }
                Data::Int(i) => {
                    level_names.push(i.to_string());
                    prev_name = Some(i.to_string());
                }
                Data::Float(f) => {
                    let title = if *f < 1.0 {
                        format!("{:.0}%", *f * 100.0)
                    } else {
                        f.to_string()
                    };
                    level_names.push(title.clone());
                    prev_name = Some(title);
                }
                _ => {
                    anyhow::bail!(
                        "单元格{}{}的值{}不是有效的字符串或数字,请检查导入文件格式",
                        index_to_alpha(col + 1),
                        row + 1,
                        cval
                    );
                }
            }
        }
        rows.push(level_names);
    }
    let rlen = rows.len();
    let clen = rows[0].len();
    let mut res: Vec<(Vec<String>, u32)> = Vec::with_capacity(rlen);
    for i in 0..clen {
        let mut vals: Vec<String> = Vec::with_capacity(rlen);
        for j in 0..rlen {
            vals.push(rows[j][i].clone());
        }
        let col_num = start_col + i as u32;
        res.push((vals, col_num));
    }
    return Ok(res);
}
